package dao.impl;

import dao.CommodityDao;
import pojo.Commodity;
import util.BaseDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

/**
 * 商品接口的实现类
 * @author 叶军军
 */
public class CommodityDaoImpl implements CommodityDao {

    /**
     *查询所有的上架商品信息
     */
    @Override
    public List<Commodity> queryAllCommodity(int state) {
        List<Commodity> list=new ArrayList<Commodity>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn= BaseDao.getConnection();
            String sql="select * from goods where go_state=?";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,state);
            rs=ps.executeQuery();
            while (rs.next()){
                Commodity commodity=new Commodity();
                commodity.setGoId(rs.getInt("go_id"));
                commodity.setGoName(rs.getString("go_name"));
                commodity.setGoSalesvolume(rs.getInt("go_salesvolume"));
                commodity.setGoPraise(rs.getInt("go_praise"));
                commodity.setGoUpTime(rs.getTimestamp("go_uptime"));
                commodity.setGoPictureUrl(rs.getString("go_pictureurl"));
                commodity.setGoDescribe(rs.getString("go_describe"));
                commodity.setGoCost(rs.getInt("go_cost"));
                commodity.setGoPrice(rs.getInt("go_price"));
                commodity.setGoStock(rs.getInt("go_stock"));
                commodity.setGoState(rs.getInt("go_state"));
                commodity.setClId(rs.getInt("cl_id"));
                commodity.setGoSeckill(rs.getInt("go_seckill"));
                list.add(commodity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return list;
    }

    /**
     *根据所属父类查询上架商品信息
     */
    @Override
    public List<Commodity> queryCommodityByClId(int clId, int state) {
        List<Commodity> list=new ArrayList<Commodity>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn= BaseDao.getConnection();
            String sql="select * from goods where go_state=? and cl_id=?";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,state);
            ps.setInt(2,clId);
            rs=ps.executeQuery();
            while (rs.next()){
                Commodity commodity=new Commodity();
                commodity.setGoId(rs.getInt("go_id"));
                commodity.setGoName(rs.getString("go_name"));
                commodity.setGoSalesvolume(rs.getInt("go_salesvolume"));
                commodity.setGoPraise(rs.getInt("go_praise"));
                commodity.setGoUpTime(rs.getTimestamp("go_uptime"));
                commodity.setGoPictureUrl(rs.getString("go_pictureurl"));
                commodity.setGoDescribe(rs.getString("go_describe"));
                commodity.setGoCost(rs.getInt("go_cost"));
                commodity.setGoPrice(rs.getInt("go_price"));
                commodity.setGoStock(rs.getInt("go_stock"));
                commodity.setGoState(rs.getInt("go_state"));
                commodity.setClId(rs.getInt("cl_id"));
                commodity.setGoSeckill(rs.getInt("go_seckill"));
                list.add(commodity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return list;
    }

    /**
     *查询所有参与秒杀活动的商品
     */
    @Override
    public List<Commodity> querySeckillCommodity() {
        List<Commodity> list=new ArrayList<Commodity>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn= BaseDao.getConnection();
            String sql="select * from goods where go_state=1 and go_seckill=1";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while (rs.next()){
                Commodity commodity=new Commodity();
                commodity.setGoId(rs.getInt("go_id"));
                commodity.setGoName(rs.getString("go_name"));
                commodity.setGoSalesvolume(rs.getInt("go_salesvolume"));
                commodity.setGoPraise(rs.getInt("go_praise"));
                commodity.setGoUpTime(rs.getTimestamp("go_uptime"));
                commodity.setGoPictureUrl(rs.getString("go_pictureurl"));
                commodity.setGoDescribe(rs.getString("go_describe"));
                commodity.setGoCost(rs.getInt("go_cost"));
                commodity.setGoPrice(rs.getInt("go_price"));
                commodity.setGoStock(rs.getInt("go_stock"));
                commodity.setGoState(rs.getInt("go_state"));
                commodity.setClId(rs.getInt("cl_id"));
                commodity.setGoSeckill(rs.getInt("go_seckill"));
                list.add(commodity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return list;
    }

    /**
     *根据搜索关键字查询商品信息
     */
    @Override
    public List<Commodity> queryCommoditiesByValue(String value) {
        List<Commodity> list=new ArrayList<Commodity>();
        Connection conn=null;
        PreparedStatement ps=null;

        try {
            conn= BaseDao.getConnection();
            String sql="select a.go_id,a.go_name,a.go_salesvolume,a.go_praise,a.go_uptime,a.go_pictureurl,a.go_describe,a.go_cost,a.go_price,a.go_stock,a.go_state,a.go_seckill " +
                    " from (select g.*,gc.* from goods g,goodsclass gc " +
                    "where g.cl_id=gc.cl_id ) a " +
                    "where a.go_name like '%"+value+"%' or a.go_describe like '%"+value+"%'  " +
                    "or a.cl_name='%"+value+"%' or a.cl_describe like '%"+value+"%'";
            ps=conn.prepareStatement(sql);
            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                Commodity commodity=new Commodity();
                commodity.setGoId(rs.getInt("go_id"));
                commodity.setGoName(rs.getString("go_name"));
                commodity.setGoSalesvolume(rs.getInt("go_salesvolume"));
                commodity.setGoPraise(rs.getInt("go_praise"));
                commodity.setGoUpTime(rs.getTimestamp("go_uptime"));
                commodity.setGoPictureUrl(rs.getString("go_pictureurl"));
                commodity.setGoDescribe(rs.getString("go_describe"));
                commodity.setGoCost(rs.getInt("go_cost"));
                commodity.setGoPrice(rs.getInt("go_price"));
                commodity.setGoStock(rs.getInt("go_stock"));
                commodity.setGoState(rs.getInt("go_state"));
                commodity.setGoSeckill(rs.getInt("go_seckill"));
                list.add(commodity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(null,ps,conn);
        }
        return list;
    }
}
